#! /usr/bin/env python
# -*- coding: utf-8 -*-
#  Auth: IAN
#  Date: 2017/5/16
#  DESC:
#
#  ---------------------------
from __future__ import division
from __future__ import print_function
from __future__ import unicode_literals
import xlrd, openpyxl
import json
import os.path
import re
import datetime
#  ---------------------------


def column_index(column_name):
    # 將column的字母名稱轉換為數值index
    column_name = column_name.upper()
    level = 0
    index = 0
    for c in reversed(list(column_name)):
        index += ord(c) - 65 + 26 * level
        level += 1
    return index

def calc_cell(cell_name):
    # 將字符CELL位置轉換為tuple index
    regex = "\d+$"
    row_name = re.findall(regex, cell_name)[0]
    row = int(row_name) - 1
    column = column_index(cell_name.split(str(row_name))[0])
    return (column, row)


class ParserXLSX(object):
    """ 解析Excel2007
    """
    def __init__(self, path):
        if not os.path.exists(path):
            raise IOError("file '%s' doesn't exists." % path)
        self.workbook = openpyxl.load_workbook(path, read_only=False)
        self._skip_list = []
        self._merged_list = {}

    def _calc_merged_cells(self):
        _merged_cells = []
        for ranges in self.sheet.merged_cell_ranges:
            begin_cell, end_cell = [calc_cell(x) for x in  ranges.split(':')]
            _merged_cells.append((begin_cell[1], end_cell[1]+1, begin_cell[0], end_cell[0]+1))
        # print(_merged_cells)
        setattr(self, "_merged_cells", _merged_cells)

    @property
    def merged_cells(self):
        if not hasattr(self, "_merged_cells"):
            self._calc_merged_cells()
        return getattr(self, "_merged_cells")

    @property
    def sheet(self):
        if not hasattr(self, "_sheet"):
            self.sheet = 0
        return getattr(self, "_sheet")

    @sheet.setter
    def sheet(self, index_or_name):
        if isinstance(index_or_name, (int, float)):
            pass
        elif isinstance(index_or_name, (str, UnicodeWarning)):
            index_or_name = self.workbook.sheetnames.index(index_or_name)
        else:
            raise ValueError
        self.xls_name = self.workbook.sheetnames[index_or_name]
        setattr(self, "_sheet", self.workbook.worksheets[index_or_name])

    def _is_merged_cell(self, row, col):
        """ 判斷一個cell是否屬於合併cell
        如果是，則將該cell及其合併的cell加入到skip list中，并計算出合併的rowspan和colspan
        """
        for merged_range in self.merged_cells:
            rs, re, cs, ce = merged_range
            if row >= rs and row < re and col >= cs and col < ce:
                for x in range(rs, re):
                    for y in range(cs, ce):
                        self._skip_list.append((x, y))
                self._merged_list[(row, col)] = dict(rowspan=(re-rs), colspan=(ce-cs), col_start=cs, col_end=ce)
                return True
        return False

    @property
    def cols(self):
        return self.sheet.max_column

    @property
    def rows(self):
        return self.sheet.max_row

    def _is_skip_cell(self, row, col):
        return (row, col) in self._skip_list

    def parser(self):

      # 計算表格中所有列的寬度
        cells_width = []
        for i in range(self.cols):
            cell_width = self.sheet.column_dimensions[chr(65 + i)].width
            cells_width.append(5.25 if cell_width is None else cell_width)

        # print(cells_width)
        body = dict()
        body["table_name"] = self.xls_name
        key_index = 0
        body["table_width"] = sum(cells_width) # 計算表格的總寬度
        for x in range(self.rows):
            for y in range(self.cols):
                if self._is_skip_cell(x, y):
                    continue

                key_name = "key%d" % key_index
                body[key_name] = {}

                value = self.sheet.cell(row=x+1, column=y+1).value
                if isinstance(value, (str ,UnicodeWarning)):
                    value = value\
                            .replace("<", "&lt;")\
                            .replace(">", "&gt;")\
                            .replace("\r\n", "<br/>")\
                            .replace("\n", "<br/>")\
                            .replace(" ", '&nbsp;')
                if not value:
                    body[key_name]["fixed"] = False
                # elif isinstance(value, (int, float)):
                #     body[key_name]["fixed"] = False

                # elif value and value.startswith("{") and value.endswith("}"):
                #     # 用來支持模板語言， 功能暫未實現
                #     body[key_name]["fixed"] = False
                else:
                    body[key_name]["fixed"] = True
                body[key_name]["value"] = value

                if self._is_merged_cell(x, y):
                    body[key_name]["rowspan"] = self._merged_list[(x, y)]["rowspan"]
                    body[key_name]["colspan"] = self._merged_list[(x, y)]["colspan"]

                    body[key_name]["width"] = sum([cells_width[i] for i in range(
                        self._merged_list[(x, y)]["col_start"],
                        self._merged_list[(x, y)]["col_end"]
                    )])
                else:
                    body[key_name]["width"] = cells_width[y]
                body[key_name]["row"] = x
                print(key_name, value)
                key_index += 1
        body["keys"] = key_index
        # print(json.dumps(body))
        return body

    def save_as_file(self, file_name="test.json"):
        with open(file_name, 'wb') as flhd:
            flhd.write(self.parser())

    def parse2html(self, json_str=None):
        if json_str is None:
            json_str = self.parser()

        html = """<table width="100%" border="1px" cellspacing="0" cellpadding="0"><tr>"""
        raw = json.loads(json_str)
        table_width = raw["table_width"]
        row = 0
        for index in range(raw["keys"]):

            key = "key%d" % index
            node = raw[key]
            if node["row"] != row:
                row = node["row"]
                html += "</tr><tr>"

            # 過濾特殊字符
            value = node["value"]
            if isinstance(value, (str,UnicodeWarning)):
                value = value\
                    .replace("<", "&lt;")\
                    .replace(">", "&gt;")\
                    .replace("\r\n", "<br/>")\
                    .replace("\n", "<br/>")\
                    .replace(" ", '&nbsp;')

            if not value:
                value = "&nbsp;"

            rowspan, colspan = node.get("rowspan", 1), node.get("colspan", 1)
            if node["fixed"]:
                html += """
                <th width='%.2f%%' colspan='%d' rowspan='%d'>%s</th>
                """ % (
                    node["width"]/1./table_width*100.,
                    colspan, rowspan, value
                )
            else:
                html += """
                <td width='%.2f%%' colspan='%d' rowspan='%d'>%s</td>
                """ % (
                    node["width"]/1./table_width*100.,
                    colspan, rowspan, value
                )

        html += "</tr></table>"
        return html
#  ---------------------------
class ParserXLS(object):
    def __init__(self, path):
        if not os.path.exists(path):
            raise IOError("file '%s' doesn't exists." % path)
        formatting_info = path.endswith(".xls")
        formatting_info = True   #读取合并单元格内容，formatting_info=False 则单元格数组为空
        self.workbook = xlrd.open_workbook(path, formatting_info=formatting_info)
        self._skip_list = []
        self._merged_list = {}
    @property
    def sheet(self):
        if not hasattr(self, "_sheet"):
            self.sheet = 0
        return getattr(self, "_sheet")
    @sheet.setter
    def sheet(self, index_or_name):
        if isinstance(index_or_name, (int,float)):
            setattr(self, "_sheet", self.workbook.sheet_by_index(index_or_name))
        elif isinstance(index_or_name, (str,UnicodeWarning)):
            setattr(self, "_sheet", self.workbook.sheet_by_name(index_or_name))
        else:
            raise TypeError("expect type str or int, received '%s'" % type(index_or_name))
    def _is_merged_cell(self, row, col):
        """ 判斷一個cell是否屬於合併cell
如果是，則將該cell及其合併的cell加入到skip list中，
并計算出合併的rowspan和colspan        """
        for merged_range in self.sheet.merged_cells:
            rs, re, cs, ce = merged_range
            if row >= rs and row < re and col >= cs and col < ce:
                for x in range(rs, re):
                    for y in range(cs, ce):
                        self._skip_list.append((x, y))
                self._merged_list[(row, col)] = dict(rowspan=(re-rs), colspan=(ce-cs), col_start=cs, col_end=ce)
                return True
        return False
    @property
    def cols(self):
        return self.sheet.ncols
    @property
    def rows(self):
        return self.sheet.nrows
    def _is_skip_cell(self, row, col):
        return (row, col) in self._skip_list
    def parser(self):
        body = dict()
        body["table_name"] = self.sheet.name
        key_index = 0
        cells_width = [self.sheet.computed_column_width(c) for c in range(self.cols)]  # 計算表格中所有列的寬度
        body["table_width"] = sum(cells_width) # 計算表格的總寬度
        for x in range(self.rows):
            for y in range(self.cols):
                if self._is_skip_cell(x, y):
                    continue
                key_name = "key%d" % key_index
                body[key_name] = {}
                value = self.sheet.cell_value(rowx=x, colx=y)
                if isinstance(value, (str ,UnicodeWarning)):
                    value = value\
                            .replace("<", "&lt;")\
                            .replace(">", "&gt;")\
                            .replace("\r\n", "<br/>")\
                            .replace("\n", "<br/>")\
                            .replace(" ", '&nbsp;')
                if self.sheet.cell(rowx=x, colx=y).ctype ==3:
                    timestamp = value
                    date_tupls = xlrd.xldate_as_tuple(timestamp,0)
                    if date_tupls[-1] == date_tupls[-2] ==date_tupls[-3]==0:
                        value = datetime.datetime(*date_tupls).strftime('%Y-%m-%d')
                    else:
                        value = datetime.datetime(*date_tupls).strftime('%Y-%m-%d %H:%M:%S')
                if not value:
                    body[key_name]["fixed"] = False
                # elif isinstance(value, (int, float)):
                #     body[key_name]["fixed"] = False
                # elif value and value.startswith("{") and value.endswith("}"):
                #     # 用來支持模板語言， 功能暫未實現
                #     body[key_name]["fixed"] = False
                else:
                    body[key_name]["fixed"] = True

                body[key_name]["value"] = value
                if self._is_merged_cell(x, y):
                    body[key_name]["rowspan"] = self._merged_list[(x, y)]["rowspan"]
                    body[key_name]["colspan"] = self._merged_list[(x, y)]["colspan"]
                    body[key_name]["width"] = sum([cells_width[i] for i in range(
                        self._merged_list[(x, y)]["col_start"],
                        self._merged_list[(x, y)]["col_end"]
                        )])
                else:
                    body[key_name]["width"] = cells_width[y]
                body[key_name]["row"] = x
                key_index += 1
        body["keys"] = key_index
        # print(json.dumps(body))
        return body
    def save_as_file(self, file_name="test.json"):
        specal=self.parser()
        print(type(specal))
        with open(file_name, 'w+') as flhd:
            flhd.write(json.dumps(specal,ensure_ascii=False))
    def parse2html(self, json_str=None):
            if json_str is None:
                json_str = self.parser()
            html = """<table width="100%" border="1px" cellspacing="0" cellpadding="0"><tr>"""
            raw = json.loads(json_str)
            table_width = raw["table_width"]
            row = 0
            for index in range(raw["keys"]):
                key = "key%d" % index
                node = raw[key]
                if node["row"] != row:
                    row = node["row"]
                    html += "</tr><tr>"
                    # 過濾特殊字符
                value = node["value"]
                if isinstance(value, (str ,UnicodeWarning)):
                    value = value\
                            .replace("<", "&lt;")\
                            .replace(">", "&gt;")\
                            .replace("\r\n", "<br/>")\
                            .replace("\n", "<br/>")\
                            .replace(" ", '&nbsp;')
                if not value:
                    value = "&nbsp;"
                    rowspan, colspan = node.get("rowspan", 1), node.get("colspan", 1)
                if node["fixed"]:
                    html += """                <th width='%.2f%%' colspan='%d' rowspan='%d'>%s</th>
"""% (
                        node["width"]/1./table_width*100.,
                        colspan, rowspan, value
                        )
                else:
                    html += """                <td width='%.2f%%' colspan='%d' rowspan='%d'>%s</td>
"""% (
                        node["width"]/1./table_width*100.,
                        colspan, rowspan, value
                        )
            html += "</tr></table>"
            return html
if __name__ == '__main__':
#     def save_as_html(file_name, table):
#        html = """<!DOCTYPE html>    <html lang="en">    <head>        <meta charset="UTF-8">        <title>Title</title>    </head>    <body>    %s    </body>    </html>
# """% table
#        with open(file_name, 'wb') as flhd:                         X
#            flhd.write(html.encode("utf8"))
    print(11)
    xls = ParserXLS("d:\\11.xls")
    print(xls. parser())
    # xls.save_as_file("tt.json")
